package model
{
	import flash.data.SQLConnection;
	import flash.data.SQLResult;
	import flash.data.SQLStatement;
	import flash.filesystem.File;
	import flash.filesystem.FileMode;
	import flash.filesystem.FileStream;
	
	import mx.collections.ArrayCollection;
	import flash.utils.ByteArray;

	public class NoteDatabase
	{
		/**sql connection*/
		private static var _sqlConnection:SQLConnection;
		[Embed(source="assets/notes.xml", mimeType="application/octet-stream")]
		private static const xmlDB:Class;
		
		/**
		* gets the current sql connection
		*/
		public static function get sqlConnection():SQLConnection
		{
			if (_sqlConnection)
				return _sqlConnection;
			openDatabase(File.applicationStorageDirectory.resolvePath("geoNotes.db"));
			return _sqlConnection;
		}
		/**
		* returns a note specified by its id
		*/
		public static function getNote(id:int):Note
		{
			var sql:String="SELECT id, title, message, datetime,lat,lon FROM notes WHERE id=?";
			var stmt:SQLStatement=new SQLStatement();
			stmt.sqlConnection=sqlConnection;
			stmt.text=sql;
			stmt.parameters[0]=id;
			stmt.execute();
			var result:Array=stmt.getResult().data;
			if (result && result.length == 1)
				return processRow(result[0]);
			else
				return null;
		}
		/**
		* returns all notes which are stored in the DB
		*/
		public static function notes():ArrayCollection
		{
			var noteList:ArrayCollection=new ArrayCollection();

			var sql:String="SELECT id, title, message, datetime,lat,lon FROM notes";
			var stmt:SQLStatement=new SQLStatement();
			stmt.sqlConnection=sqlConnection;
			stmt.text=sql;
			stmt.execute();
			var sqlResult:SQLResult=stmt.getResult();
			if (sqlResult)
			{
				var result:Array=sqlResult.data;
				if (result)
				{
					for (var index:Number=0; index < result.length; index++)
					{
						noteList.addItem(processRow(result[index]));
					}
				}
			}
			return noteList;
		}
		/**
		* inserts a note in the DB
		*/
		public static function addNote(note:Note):void
		{
			var sql:String="INSERT INTO notes (title, message, datetime,lat,lon) " + "VALUES (?,?,?,?,?)";
			var stmt:SQLStatement=new SQLStatement();
			stmt.sqlConnection=sqlConnection;
			stmt.text=sql;
			stmt.parameters[0]=note.title;
			stmt.parameters[1]=note.message;
			stmt.parameters[2]=note.datetime;
			stmt.parameters[3]=note.lat;
			stmt.parameters[4]=note.lon;
			stmt.execute();
		}
		/**
		* deletes a note from the db
		*/
		public static function deleteNote(note:Note):void
		{
			var sql:String="DELETE FROM notes WHERE id=?";
			var stmt:SQLStatement=new SQLStatement();
			stmt.sqlConnection=sqlConnection;
			stmt.text=sql;
			stmt.parameters[0]=note.id;
			stmt.execute();
		}
		/**
		* helper method to process a row
		*/
		protected static function processRow(o:Object):Note
		{
			var note:Note=new Note();
			note.id=o.id;
			note.title=o.title == null ? "" : o.title;
			note.message=o.message == null ? "" : o.message;
			note.datetime=o.datetime == null ? "" : o.datetime;
			note.lat=o.lat == null ? 0.0 : o.lat;
			note.lon=o.datetime == null ? 0.0 : o.lon;
			return note;
		}
		/**
		* opens a DB connection
		*/
		public static function openDatabase(file:File):void
		{
			var newDB:Boolean=true;
			if (file.exists)
				newDB=false;
			_sqlConnection=new SQLConnection();
			_sqlConnection.open(file);
			if (newDB)
			{
				createDatabase();
				populateDatabase();
			}
		}
		/**
		* creates a DB to store data
		*/
		protected static function createDatabase():void
		{
			var sql:String="CREATE TABLE IF NOT EXISTS notes ( " + "id INTEGER PRIMARY KEY AUTOINCREMENT, " + "title VARCHAR(50), " + "message VARCHAR(200)," + "datetime VARCHAR(100),lat FLOAT,lon FLOAT)";
			var stmt:SQLStatement=new SQLStatement();
			stmt.sqlConnection=sqlConnection;
			stmt.text=sql;
			stmt.execute();
		}
		/**
		* inserts some fake data into the DB for debug purpose
		*/
		protected static function populateDatabase():void
		{
			var byteArray:ByteArray = new xmlDB() as ByteArray;
			var xml:XML = new XML(byteArray.readUTFBytes(byteArray.length));
			for each (var n:XML in xml.note)
			{
				var note:Note=new Note();
				note.id=n.id;
				note.title=n.title;
				note.message=n.message;
				note.datetime=n.datetime;
				note.lat=n.lat;
				note.lon=n.lon;
				addNote(note);
			}
		}

	}
}
